ALTER	proc sp_products_list_with_odm_part_numbers
@customer_id  int, @odm_id int, @is_sample int
as
begin
	declare @results Table(ProcessStep_ID int, IST_Manufacturing_Part_Number varchar (50), IST_Marketing_Part_Number varchar (50), Product_ID int, P_Type varchar (50), P_Comments varchar (200),Customer_PartNumber varchar(200),Label_Number varchar(50) )
	declare @proc_id varchar (20)
	declare @mft_part_no varchar (50)
	declare @mkt_part_no varchar (50)
	declare @prod_id int
	declare @p_type varchar (50)
	declare @p_description varchar (200)
	declare @customer_partnumber varchar(200)
	declare @label_number varchar(50)
	if @odm_id <= 0
	begin
		set @odm_id = null
	end
	-- Added by Sarada on 6th jan 2004
	insert into @results values(null,'---- CHIPS -----','---- CHIPS -----',null,null,null,null,null)
	
	if( @is_sample = 0 )
	begin
		declare ncursor cursor for 
		select Process_Step.ProcessStep_ID, Product.Part_Number, Product.P_Marketing_part_Number, Product.Product_ID, Product.P_Type, Product.P_Comments from product,Process_Step  where product.P_Type is null and product.ProcessStep_ID in (select processstep_id from Process_Step where PS_Type_Name like 'Finished Goods') and product.processStep_id=Process_Step.ProcessStep_ID order by Product.Part_Number
	end
	else
	begin
		declare ncursor cursor for 
		select Process_Step.ProcessStep_ID, Product.Part_Number, Product.P_Marketing_part_Number, Product.Product_ID, Product.P_Type, Product.P_Comments from product,Process_Step  where product.P_Type is null and product.Is_Approved = 0 and product.ProcessStep_ID in (select processstep_id from Process_Step where PS_Type_Name like 'Finished Goods') and product.processStep_id=Process_Step.ProcessStep_ID order by Product.Part_Number
	end

	--SELECT inv.ProcessStep_ID ProcessStep_ID,IST_Manufacturing_Part_Number,IST_Marketing_Part_Number,product.Product_ID, P_Type FROM Inventory_Step_Table inv ,Product,Process_Step  where Process_Step.Ps_Type = 'Finished Goods' and inv.ProcessStep_ID = product.ProcessStep_ID and inv.ProcessStep_ID = Process_Step.ProcessStep_ID and p_type IS NULL 
	open ncursor
		fetch next from ncursor into @proc_id,@mft_part_no,@mkt_part_no,@prod_id,@p_type, @p_description 
		select @customer_partnumber = customer_partnumber, @label_number = Label_Number from Customer_PartNumbers where product_id = @prod_id and Customer_ID = @customer_id and ODM_Id is NULL
		if @odm_id IS NOT NULL   
		begin
			set @label_number = null
			set @customer_partnumber = null
			select @customer_partnumber = customer_partnumber, @label_number = Label_Number from Customer_PartNumbers where product_id = @prod_id and Customer_ID = @customer_id and ODM_Id = @odm_id
		end
		while @@fetch_status = 0
		begin
			insert into @results values(@proc_id,@mft_part_no,@mkt_part_no,@prod_id,@p_type,@p_description,@customer_partnumber,@label_number)
			set @proc_id = null
			set @mft_part_no = null
			set @mkt_part_no = null
			set @prod_id = null
			set @p_type = null
			set @p_description = null
			set @customer_partnumber = null
			set @label_number = null
			fetch next from ncursor into @proc_id,@mft_part_no,@mkt_part_no,@prod_id,@p_type, @p_description 
			select @customer_partnumber = customer_partnumber, @label_number = Label_Number from Customer_PartNumbers where product_id = @prod_id and Customer_ID = @customer_id and ODM_Id is NULL
			if @odm_id IS NOT NULL  
			begin
				set @label_number = null
				set @customer_partnumber = null
				select @customer_partnumber = customer_partnumber, @label_number = Label_Number from Customer_PartNumbers where product_id = @prod_id and Customer_ID = @customer_id and ODM_Id = @odm_id
			end
		end
	close ncursor
	deallocate ncursor
	insert into @results values(null,'---- BOARD -----','---- BOARD -----',null,null,null,null,null)

	if( @is_sample = 0 )
	begin
		declare bcursor cursor for
		select Process_Step.ProcessStep_ID, Product.Part_Number, Product.P_Marketing_part_Number, Product.Product_ID, Product.P_Type,Product.P_Comments from product,Process_Step  where product.P_Type = 'BOARD' and product.ProcessStep_ID in (select processstep_id from Process_Step where PS_Type_Name like 'Finished Goods') and product.processStep_id=Process_Step.ProcessStep_ID and Product.P_Parent_Product_ID = -1 order by Product.Part_Number
	end
	else
	begin
		declare bcursor cursor for
		select Process_Step.ProcessStep_ID, Product.Part_Number, Product.P_Marketing_part_Number, Product.Product_ID, Product.P_Type,Product.P_Comments from product,Process_Step  where product.P_Type = 'BOARD' and product.Is_Approved = 0 and product.ProcessStep_ID in (select processstep_id from Process_Step where PS_Type_Name like 'Finished Goods') and product.processStep_id=Process_Step.ProcessStep_ID and Product.P_Parent_Product_ID = -1 order by Product.Part_Number
	end

	open bcursor
		
		fetch next from bcursor into @proc_id,@mft_part_no,@mkt_part_no,@prod_id,@p_type,@p_description 
		select @customer_partnumber = customer_partnumber, @label_number = Label_Number from Customer_PartNumbers where product_id = @prod_id and Customer_ID = @customer_id and ODM_Id is NULL
		if @odm_id IS NOT NULL 
		begin
			set @label_number = null
			set @customer_partnumber = null
			select @customer_partnumber = customer_partnumber, @label_number = Label_Number from Customer_PartNumbers where product_id = @prod_id and Customer_ID = @customer_id and ODM_Id = @odm_id
		end
		while @@fetch_status = 0
		begin
			-- Added by Prabhanjan (15 Apr 2004)
			declare childCursor cursor for
			select Process_Step.ProcessStep_ID, Product.Part_Number, Product.P_Marketing_part_Number, Product.Product_ID, Product.P_Type,Product.P_Comments from product,Process_Step where product.P_Type = 'BOARD' and product.Is_Approved = 0 and product.ProcessStep_ID in (select processstep_id from Process_Step where PS_Type_Name like 'Finished Goods') and product.processStep_id=Process_Step.ProcessStep_ID and Product.P_Parent_Product_ID = @prod_id order by Product.P_Marketing_part_Number
			
			open childCursor			
			fetch next from childCursor into @proc_id,@mft_part_no,@mkt_part_no,@prod_id,@p_type,@p_description
			--Added by veeru on 15th June 2004			
			select @customer_partnumber = customer_partnumber, @label_number = Label_Number from Customer_PartNumbers where product_id = @prod_id and Customer_ID = @customer_id and ODM_Id is NULL
			if @odm_id IS NOT NULL  
			begin
				set @label_number = null
				set @customer_partnumber = null
				select @customer_partnumber = customer_partnumber, @label_number = Label_Number from Customer_PartNumbers where product_id = @prod_id and Customer_ID = @customer_id and ODM_Id = @odm_id
			end
			
			if @@fetch_status <> 0
			begin
				insert into @results values(@proc_id,@mft_part_no,@mkt_part_no,@prod_id,@p_type,@p_description,@customer_partnumber,@label_number)
			end

			while @@fetch_status = 0
			begin
				insert into @results values(@proc_id,@mft_part_no,@mkt_part_no,@prod_id,@p_type,@p_description,@customer_partnumber,@label_number)
				set @proc_id = null
				set @mft_part_no = null
				set @mkt_part_no = null
				set @prod_id = null
				set @p_type = null
				set @p_description = null
				set @customer_partnumber = null
				set @label_number = null
	
				fetch next from childCursor into @proc_id,@mft_part_no,@mkt_part_no,@prod_id,@p_type,@p_description
				--Added by veeru on 15th June 2004			
				select @customer_partnumber = customer_partnumber, @label_number = Label_Number from Customer_PartNumbers where product_id = @prod_id and Customer_ID = @customer_id and ODM_Id is NULL
				if @odm_id IS NOT NULL  
				begin
					set @label_number = null
					set @customer_partnumber = null
					select @customer_partnumber = customer_partnumber, @label_number = Label_Number from Customer_PartNumbers where product_id = @prod_id and Customer_ID = @customer_id and ODM_Id = @odm_id
				end
			end
			close childCursor
			deallocate childCursor
			
			set @proc_id = null
			set @mft_part_no = null
			set @mkt_part_no = null
			set @prod_id = null
			set @p_type = null
			set @p_description = null
			set @customer_partnumber = null
			set @label_number = null
			fetch next from bcursor into @proc_id,@mft_part_no,@mkt_part_no,@prod_id,@p_type,@p_description 
			select @customer_partnumber = customer_partnumber, @label_number = Label_Number from Customer_PartNumbers where product_id = @prod_id and Customer_ID = @customer_id and ODM_Id is NULL
			if @odm_id IS NOT NULL 
			begin
				set @label_number = null
				set @customer_partnumber = null
				select @customer_partnumber = customer_partnumber,@label_number = Label_Number from Customer_PartNumbers where product_id = @prod_id and Customer_ID = @customer_id and ODM_Id = @odm_id
			end
		end
	close bcursor
	deallocate bcursor
	insert into @results values(null,'---- KIT -----','---- KIT -----',null,null,null,null,null)

	if( @is_sample = 0 )
	begin
		declare kcursor cursor for
		select Process_Step.ProcessStep_ID, Product.Part_Number, Product.P_Marketing_part_Number, Product.Product_ID, Product.P_Type,Product.P_Comments from product,Process_Step  where product.P_Type = 'KIT' and product.ProcessStep_ID in (select processstep_id from Process_Step where PS_Type_Name like 'Finished Goods') and product.processStep_id=Process_Step.ProcessStep_ID order by Product.Part_Number
	end
	else
	begin
		declare kcursor cursor for
		select Process_Step.ProcessStep_ID, Product.Part_Number, Product.P_Marketing_part_Number, Product.Product_ID, Product.P_Type,Product.P_Comments from product,Process_Step  where product.P_Type = 'KIT' and product.Is_Approved = 0 and product.ProcessStep_ID in (select processstep_id from Process_Step where PS_Type_Name like 'Finished Goods') and product.processStep_id=Process_Step.ProcessStep_ID order by Product.Part_Number
	end
	open kcursor
		fetch next from kcursor into @proc_id,@mft_part_no,@mkt_part_no,@prod_id,@p_type,@p_description 
		select @customer_partnumber = customer_partnumber, @label_number = Label_Number from Customer_PartNumbers where product_id = @prod_id and Customer_ID = @customer_id and ODM_Id is NULL
		if @odm_id IS NOT NULL 
		begin
			set @label_number = null
			set @customer_partnumber = null
			select @customer_partnumber = customer_partnumber, @label_number = Label_Number from Customer_PartNumbers where product_id = @prod_id and Customer_ID = @customer_id and ODM_Id = @odm_id
		end
		while @@fetch_status = 0
		begin
			insert into @results values(@proc_id,@mft_part_no,@mkt_part_no,@prod_id,@p_type,@p_description,@customer_partnumber,@label_number )
			set @proc_id = null
			set @mft_part_no = null
			set @mkt_part_no = null
			set @prod_id = null
			set @p_type = null
			set @p_description = null
			set @customer_partnumber = null
			set @label_number = null
			fetch next from kcursor into @proc_id,@mft_part_no,@mkt_part_no,@prod_id,@p_type,@p_description 
			select @customer_partnumber = customer_partnumber, @label_number = Label_Number from Customer_PartNumbers where product_id = @prod_id and Customer_ID = @customer_id and ODM_Id is NULL
			if @odm_id IS NOT NULL  
			begin
				set @label_number = null
				set @customer_partnumber = null
				select @customer_partnumber = customer_partnumber, @label_number = Label_Number from Customer_PartNumbers where product_id = @prod_id and Customer_ID = @customer_id and ODM_Id = @odm_id
			end
		end
	close kcursor
	deallocate kcursor
	select * from @results 
end
